package DBLayer;

import ModelLayer.*;

import java.sql.*;

/**
 * @author Gruppe 2
 *
 */
public class DBOrder implements IFDBOrder {

	private Connection con;

	public DBOrder() {
		con = DbConnection.getInstance().getDBcon();
	}

	public SalesOrder selectSingleOrder(int orderNo) throws SQLException {
		SalesOrder salesOrder = null;
		try {
			String query = "SELECT orderNo, salesOrderDate, salesOrderStatus, deliveryDate, invoiceNo, "
					+ "customerId FROM SalesOrder WHERE orderNo=?";
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, orderNo);
			ResultSet result = ps.executeQuery();
			if (result.next()) {
				salesOrder = buildOrder(result);
			}

			String query1 = "SELECT id, productId, quantity FROM SalesLine where SalesOrderNo = ?";
			PreparedStatement ps1 = con.prepareStatement(query1);
			ps1.setInt(1, orderNo);
			ResultSet rs1 = ps1.executeQuery();
			while (rs1.next()) {
				salesOrder.addSalesLine(buildSalesLine(rs1, salesOrder));
			}
		} catch (SQLException e) {
			throw e;
		}
		// TODO Auto-generated method stub
		return salesOrder;

	}

	private SalesOrder buildOrder(ResultSet results) throws SQLException {
		SalesOrder saleObj = new SalesOrder();
		try {
			saleObj.setOrderNo(results.getInt("orderNo"));
			saleObj.setSalesOrderDate(results.getString("salesOrderDate"));
			saleObj.setSalesOrderStatus(results.getString("salesOrderStatus"));
			saleObj.setDeliveryDate(results.getString("deliveryDate"));
			saleObj.setInvoiceNo(results.getInt("invoiceNo"));
			saleObj.setCustomer(new Customer());
			saleObj.getCustomer().setId(results.getInt("customerId"));
		} catch (Exception e) {
			System.out.println("error in building the order object");
			throw e;
		}
		return saleObj;

	}

	private SalesLine buildSalesLine(ResultSet results, SalesOrder order)
			throws SQLException {
		SalesLine sl = new SalesLine();
		try {
			sl.setId(results.getInt("id"));
			sl.setProduct(new Product());
			sl.getProduct().setProductId(results.getInt("productId"));
			sl.setSalesOrder(order);
			sl.setQuantity(results.getInt("quantity"));
		} catch (SQLException e) {
			throw e;
		}
		return sl;
	}

	public void updateSalesOrder(SalesOrder order) throws SQLException {
		SalesOrder saleObj = order;

		String query = "UPDATE SalesOrder SET " + "salesOrderStatus ='"
				+ saleObj.getSalesOrderStatus() + "', " + "deliveryDate ='"
				+ saleObj.getDeliveryDate() + "' " + " WHERE orderNo = '"
				+ saleObj.getOrderNo() + "'";
		//System.out.println("Update query:" + query);
		try { // update SalesOrder
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);

			stmt.close();

			// SalesLines need to be updated as well...

		}// slut try
		catch (SQLException ex) {
			throw ex;
		}
	}

	@Override
	public void insertSalesOrder(SalesOrder order) throws SQLException {
		try {
			String query = "INSERT INTO SalesOrder (orderNo,salesOrderDate,"
					+ "salesOrderStatus,deliveryDate,invoiceNo,customerId) "
					+ "values (?,?,?,?,?,?)";
			DbConnection.startTransaction();
			int nextId = GetMax
					.getMaxId("SELECT max(OrderNo)+1 FROM SalesOrder");
			order.setOrderNo(nextId);
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, nextId);
			ps.setString(2, order.getSalesOrderDate());
			ps.setString(3, order.getSalesOrderStatus());
			ps.setString(4, order.getDeliveryDate());
			if (order.getInvoiceNo() > 0) {
				ps.setInt(5, order.getInvoiceNo());
			} else {
				ps.setNull(5, java.sql.Types.INTEGER);
			}
			ps.setInt(6, order.getCustomer().getId());
			ps.executeUpdate();
			for (SalesLine sl : order.getSalesLineArray()) {
				sl.setId(nextId);
				insertSalesLine(sl, true);
			}
			DbConnection.commitTransaction();
		} catch (SQLException e) {
			DbConnection.rollbackTransaction();
			throw e;
		}
		// TODO Auto-generated method stub

	}

	private void insertSalesLine(SalesLine sl, boolean rollback)
			throws SQLException {
		try {
			String query = "INSERT INTO SalesLine (id, salesOrderNo, productId, quantity) "
					+ "Values (?,?,?,?)";
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, GetMax.getMaxId("SELECT max(id)+1 FROM SalesLine"));
			ps.setInt(2, sl.getSalesOrder().getOrderNo());
			ps.setInt(3, sl.getProduct().getProductId());
			ps.setInt(4, sl.getQuantity());
			ps.executeUpdate();
		} catch (SQLException e) {
			if (rollback) {
				DbConnection.rollbackTransaction();
			}
			throw e;
		}
	}

}
